Os dados utilizados nessa análise são provenientes do banco de dados do TCE/PB, mais específicamente as bases de dados cabobranco e vigia, as quais são utilizadas na aplicação GEO Obras.
Como existem 46 tabelas nesse banco de dados, analisaremos apenas as que estão bastante atreladas as obras, visto que neste banco de dados existem também outros dados necessários para a aplicação rodar. Com isso, analisaremos a tabela obra, as que são referenciadas por esta, bem como a tabela de acompanhamento.
Vejamos a tabela de obras:
obra %>%
str()
## 'data.frame': 28681 obs. of 16 variables:
## $ id : num 1 2 3 4 5 7 8 12 13 14 ...
## $ fk_jurisdicionado : num 634 587 587 794 802 796 631 749 684 668 ...
## $ numero_contrato : chr "402012016" "000522016" "000532016" "000992016" ...
## $ numero_obra : chr "00022016" "00012016" "00022016" "00022016" ...
## $ descricao_sucinta_obra : chr "Construção de muro de contorno da Escola Francisco Chaves Ventura no Município de Camalaú." "REFORMA E AMPLIAÇÃO DA ESCOLA LÍDIA CABRAL DE SOUSA AGUIAR PB" "CONTINUIDADE DA CONSTRUÇÃO DE UM CAMPO DE FUTEBOL" "AMPLIAÇÃO DA ESCOLA PADRE IBIAPINA – SANTA FÉ – SOLÂNEA," ...
## $ fk_localidade : num 1341 1293 1293 1384 1338 ...
## $ descricao_localidade : chr "Rua Projetada, s/n, Centro, Camalaú-PB" "RUA SEVERINO AMÂNCIO, S/N, NO MUNICÍPIO DE AGUIAR" "RUA SEVERINO AMÂNCIO, S/N, NO MUNICÍPIO DE AGUIAR PB" "SANTA FÉ - SOLÂNEA" ...
## $ fk_tipo_obra : int 24 24 24 24 1 1 24 1 10 1 ...
## $ fk_tipo_execucao : chr "[1]" "[2,3]" "[1]" "[2]" ...
## $ valor_obra : num 146656 358546 314988 139158 127169 ...
## $ planilha_contratada : chr "Proposta Caipira - Contorno do Muro.xlsx" "cmd.exe" "cmd.exe" "ESCOLA SANTA FÉ - BM1_.xls" ...
## $ obra_incorporavel_patrimonio: logi TRUE TRUE TRUE TRUE TRUE TRUE ...
## $ dimensao : num 374 532 440 213 1 ...
## $ numero_protocolo_tramita : chr "49496/16" "49641/16" "49653/16" "49810/16" ...
## $ fk_complexo_obras : num 1 2 3 4 5 7 8 12 13 14 ...
## $ cancelled : logi FALSE FALSE FALSE FALSE FALSE FALSE ...
Na tabela de obras existem 16 colunas, onde temos:
Como falado acima, existe uma chave estrangeira para o jurisdicionado, mas a tabela de jurisdicionado do db vigia é vazia.
jurisdicionado_db1 %>% count()
## # A tibble: 0 x 0
Com isso, ela provavelmente se refere à tabela do db cabobranco, visto que existem observações no segundo.
jurisdicionado_db2 %>% count()
## # A tibble: 1 x 1
## n
## <int>
## 1 1048
A chave estrangeira fk_tipo_obra não é encontrada em nenhuma outra tabela, assim como fk_tipo_execucao. Apesar de existir uma coluna planilha_contratada na tabela obras, não foi encontrada outra tabela que contenha estas planilhas.
Vejamos a tabela de localidade, referente a chave estrangeira fk_localidade da tabela obras:
localidade %>%
str()
## 'data.frame': 5566 obs. of 16 variables:
## $ id : num 1313 1293 1390 1391 1398 ...
## $ uf : chr "Paraíba" "Paraíba" "Paraíba" "Paraíba" ...
## $ codigo_uf : int 25 25 25 25 25 25 25 25 25 25 ...
## $ mesoregiao : chr "Sertão Paraibano" "Sertão Paraibano" "Agreste Paraibano" "Agreste Paraibano" ...
## $ codigo_mesoregiao : int 2501 2501 2503 2503 2503 2502 2503 2504 2503 2503 ...
## $ microregiao : chr "Serra do Teixeira" "Piancó" "Brejo Paraibano" "Brejo Paraibano" ...
## $ codigo_microregiao: int 25007 25005 25015 25015 25016 25011 25012 25023 25016 25012 ...
## $ nome : chr "Água Branca" "Aguiar" "Alagoa Grande" "Alagoa Nova" ...
## $ codigo_ibge : int 2500106 2500205 2500304 2500403 2500502 2500536 2500577 2500601 2500809 2500908 ...
## $ codigo_siaf : int 0 981903 981905 981907 981909 980440 980442 981911 981915 981917 ...
## $ link_ibge : chr "http://cidades.ibge.gov.br/xtras/perfil.php?codmun=2500106" "http://cidades.ibge.gov.br/xtras/perfil.php?codmun=2500205" "http://cidades.ibge.gov.br/xtras/perfil.php?codmun=2500304" "http://cidades.ibge.gov.br/xtras/perfil.php?codmun=2500403" ...
## $ bandeira : chr "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ ...
## $ brasao : chr "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ ...
## $ link_wikipedia : chr "https://pt.wikipedia.org/wiki/%C3%81gua_Branca_(Para%C3%ADba)" "https://pt.wikipedia.org/wiki/Aguiar_(Para%C3%ADba)" "https://pt.wikipedia.org/wiki/Alagoa_Grande" "https://pt.wikipedia.org/wiki/Alagoa_Nova" ...
## $ esfera : int 1 1 1 1 1 1 1 1 1 1 ...
## $ cancelled : logi FALSE FALSE FALSE FALSE FALSE FALSE ...
Na tabela de localidades também existem 16 colunas, onde temos:
Vejamos agora a tabela de jurisdicionados, referente à chave estrangeira de obras:
jurisdicionado_db2 %>%
str()
## 'data.frame': 1048 obs. of 12 variables:
## $ id : num 855 839 877 316 291 ...
## $ pessoajuridica : num 855 839 877 316 291 ...
## $ poder : int 1 1 1 1 1 1 1 1 1 1 ...
## $ tipojurisdicionado_fk: num 13 13 2 5 10 13 2 13 10 13 ...
## $ localidade_fk : num 5566 5566 5566 5566 5566 ...
## $ nome : chr "Secretaria de Estado da Receita" "Secretaria de Estado do Desenvolvimento e da Articulação Municipal" "Superintendência de Administração do Meio Ambiente" "Fundação Estadual do Bem Estar do Menor Alice de Almeida" ...
## $ codigo_sagres : chr "0" "0" "0" "0" ...
## $ previdenciario : logi FALSE FALSE FALSE FALSE FALSE FALSE ...
## $ municipio_importacao : chr "Paraíba" "Paraíba" "Paraíba" "Paraíba" ...
## $ tramita_id : num 855 839 877 316 291 ...
## $ cancelled : logi FALSE FALSE FALSE FALSE FALSE FALSE ...
## $ esfera : int 2 2 2 2 2 2 2 2 2 2 ...
Na tabela de jurisdicionados existem 12 colunas, onde temos:
O nome do jurisdicionado parece coincidir com a unidade gestora do banco de dados do SAGRES.
Vejamos a tabela de complexos, referente à chave estrangeira de obras:
complexo %>%
str()
## 'data.frame': 720 obs. of 5 variables:
## $ id : num 160 164 350 435 442 237 290 332 365 366 ...
## $ descricao: chr "REFORMA DA FARMACIA" "prefeitura" "Cconstrução de uma creche, tipo 02, na zona urbana do Município de Boa Vista/PB" "CONSTRUÇÃO DE 03 (TRÊS) SALAS PARA ATENDIMENTO À SECRETARIA DE AGRICULTURA" ...
## $ tipo : chr "OUTRAS" "OUTRAS" "CRECHE" "OUTRAS" ...
## $ codigo : chr "1601" "1649" "3505" "4354" ...
## $ cancelled: logi FALSE FALSE FALSE FALSE FALSE FALSE ...
Na tabela de complexos existem 5 colunas, onde temos:
Podemos ver que a tabela de complexo complementa a de obra, visto que tem o tipo da obra. Além disso, cada complexo é referenciado por mais de uma obra, como podemos ver a seguir pela diferenca entre a chave estrangeira em obras (existem 28681 linhas) e o id em complexos (existem 720 linhas):
obra %>%
filter(!is.na(fk_complexo_obras)) %>%
nrow()
## [1] 28681
complexo %>%
nrow()
## [1] 720
Vejamos a tabela de acompanhamento, a que de fato contém os pontos georeferenciados.
acompanhamento %>%
str()
## 'data.frame': 56218 obs. of 7 variables:
## $ id : num 1 2 3 4 5 6 7 8 9 10 ...
## $ tipo : int 1 1 1 1 2 2 2 2 1 1 ...
## $ tipo_georeferenciamento : int 3 2 1 2 2 2 2 2 2 2 ...
## $ valor_georeferenciamento: chr "[-6.896274219677315, -38.57133507728577]" "Quadra Coberta Com Vestiário.kml" "[-7.214552, -38.586303]" "PAVIMENTAÇÃO DE RUAS E LADEIRAS.kml" ...
## $ fk_obra : num 8 24 34 22 22 22 22 8 12 59 ...
## $ cancelled : logi FALSE FALSE FALSE FALSE FALSE FALSE ...
## $ data_cadastro : Date, format: NA NA ...
Na tabela de acompanhamentos existem 5 colunas, onde temos:
Vejamos os tipos de acompanhamento
acompanhamento %>%
arrange(valor_georeferenciamento) %>%
head() %>%
kable()
| id | tipo | tipo_georeferenciamento | valor_georeferenciamento | fk_obra | cancelled | data_cadastro |
|---|---|---|---|---|---|---|
| 68 | 2 | 0 | 0 | 340 | FALSE | 2007-04-02 |
| 69 | 1 | 0 | 0 | 340 | FALSE | 2007-04-02 |
| 70 | 2 | 0 | 0 | 341 | FALSE | 2004-02-02 |
| 71 | 1 | 0 | 0 | 341 | FALSE | 2004-02-02 |
| 72 | 2 | 0 | 0 | 342 | FALSE | 2007-07-20 |
| 73 | 1 | 0 | 0 | 342 | FALSE | 2007-07-20 |
acompanhamento %>%
filter(tipo_georeferenciamento == 1) %>%
arrange(valor_georeferenciamento) %>%
head() %>%
kable()
| id | tipo | tipo_georeferenciamento | valor_georeferenciamento | fk_obra | cancelled | data_cadastro |
|---|---|---|---|---|---|---|
| 56053 | 3 | 1 | [0, 0] | 16890 | FALSE | 2017-08-18 |
| 56054 | 3 | 1 | [0, 0] | 16915 | FALSE | 2017-08-18 |
| 354 | 1 | 1 | [ [2.29455010034144 48.8582710828632], [-94.799 38.8556 324.9168], [-1.4894 50.9017 35.9664], [121.6403 25.0618 38.1], [-38.162088, -6.940324 261.834]] | 485 | FALSE | 2012-11-10 |
| 364 | 2 | 1 | [ [2.29455010034144 48.8582710828632], [-94.799 38.8556 324.9168], [-1.4894 50.9017 35.9664], [121.6403 25.0618 38.1], [-38.162088, -6.940324 261.834]] | 485 | FALSE | 2012-11-10 |
| 1201 | 1 | 1 | [ [2.29455010034144 48.8582710828632], [-94.799 38.8556 324.9168], [-1.4894 50.9017 35.9664], [121.6403 25.0618 38.1], [-38.165843, -6.94268 260.7706]] | 908 | FALSE | 2006-03-01 |
| 1208 | 2 | 1 | [ [2.29455010034144 48.8582710828632], [-94.799 38.8556 324.9168], [-1.4894 50.9017 35.9664], [121.6403 25.0618 38.1], [-38.165843, -6.94268 260.7706]] | 908 | FALSE | 2006-03-01 |
acompanhamento %>%
filter(tipo_georeferenciamento == 2) %>%
head() %>%
kable()
| id | tipo | tipo_georeferenciamento | valor_georeferenciamento | fk_obra | cancelled | data_cadastro |
|---|---|---|---|---|---|---|
| 2 | 1 | 2 | Quadra Coberta Com Vestiário.kml | 24 | FALSE | NA |
| 4 | 1 | 2 | PAVIMENTAÇÃO DE RUAS E LADEIRAS.kml | 22 | FALSE | NA |
| 5 | 2 | 2 | PAVIMENTAÇÃO DE RUAS E LADEIRAS.kml | 22 | FALSE | NA |
| 6 | 2 | 2 | PAVIMENTAÇÃO DE RUAS E LADEIRAS.kml | 22 | FALSE | NA |
| 7 | 2 | 2 | PAVIMENTAÇÃO DE RUAS E LADEIRAS.kml | 22 | FALSE | NA |
| 8 | 2 | 2 | UNIDADE DE ACOLHIMENTO.kml | 8 | FALSE | NA |
acompanhamento %>%
filter(tipo_georeferenciamento == 3) %>%
head() %>%
kable()
| id | tipo | tipo_georeferenciamento | valor_georeferenciamento | fk_obra | cancelled | data_cadastro |
|---|---|---|---|---|---|---|
| 1 | 1 | 3 | [-6.896274219677315, -38.57133507728577] | 8 | FALSE | NA |
| 13 | 1 | 3 | [-7.043786383642173, -37.27304399013519] | 140 | FALSE | NA |
| 14 | 1 | 3 | [-7.893936046154858, -37.118151783943176] | 86 | FALSE | NA |
| 15 | 1 | 3 | [-7.82752685577032, -37.16200053691864] | 144 | FALSE | NA |
| 16 | 2 | 3 | [-7.043749116121552, -37.273065447807305] | 140 | FALSE | NA |
| 19 | 1 | 3 | [-6.890394712764171, -37.72682547569275] | 186 | FALSE | NA |
Vemos que a geolocalização pode ser de quatro tipos, onde o 0 é quando não existe georeferenciamento, 1 é uma lista de pares latitude e longitude, o tipo 2 é uma referência para um arquivo KML, o qual não foi encontrado na base de dados, e o tipo 3 é apenas um par de latitude e longitude.
Vejamos os dados faltantes na tabela de obras:
anomalies.obra <- anomalies(obra)
## Warning in anomalies(obra): Found 3 possible problematic variables:
## cancelled, dimensao, obra_incorporavel_patrimonio
anomalies.obra$variables %>%
kable()
| Variable | q | qNA | pNA | qZero | pZero | qBlank | pBlank | qInf | pInf | qDistinct | type | anomalous_percent |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| cancelled | 28681 | 0 | - | 28424 | 99.1% | 0 | - | 0 | - | 2 | Logical | 99.1% |
| dimensao | 28681 | 0 | - | 27925 | 97.36% | 0 | - | 0 | - | 616 | Numeric | 97.36% |
| obra_incorporavel_patrimonio | 28681 | 0 | - | 26182 | 91.29% | 0 | - | 0 | - | 2 | Logical | 91.29% |
| valor_obra | 28681 | 0 | - | 22676 | 79.06% | 0 | - | 0 | - | 4311 | Numeric | 79.06% |
| numero_protocolo_tramita | 28681 | 13 | 0.05% | 0 | - | 0 | - | 0 | - | 1265 | Character | 0.05% |
| fk_tipo_execucao | 28681 | 0 | - | 0 | - | 0 | - | 0 | - | 26 | Character | - |
| fk_tipo_obra | 28681 | 0 | - | 0 | - | 0 | - | 0 | - | 37 | Integer | - |
| fk_localidade | 28681 | 0 | - | 0 | - | 0 | - | 0 | - | 221 | Numeric | - |
| fk_jurisdicionado | 28681 | 0 | - | 0 | - | 0 | - | 0 | - | 498 | Numeric | - |
| fk_complexo_obras | 28681 | 0 | - | 0 | - | 0 | - | 0 | - | 720 | Numeric | - |
| planilha_contratada | 28681 | 0 | - | 0 | - | 0 | - | 0 | - | 1244 | Character | - |
| numero_contrato | 28681 | 0 | - | 0 | - | 0 | - | 0 | - | 3665 | Character | - |
| numero_obra | 28681 | 0 | - | 0 | - | 0 | - | 0 | - | 4507 | Character | - |
| descricao_localidade | 28681 | 0 | - | 0 | - | 0 | - | 0 | - | 14346 | Character | - |
| descricao_sucinta_obra | 28681 | 0 | - | 0 | - | 0 | - | 0 | - | 25951 | Character | - |
| id | 28681 | 0 | - | 0 | - | 0 | - | 0 | - | 28681 | Numeric | - |
anomalies.obra$problem_variables %>%
kable()
| Variable | q | qNA | pNA | qZero | pZero | qBlank | pBlank | qInf | pInf | qDistinct | type | anomalous_percent | problems |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| cancelled | 28681 | 0 | - | 28424 | 99.1% | 0 | - | 0 | - | 2 | Logical | 99.1% | Anomalies present in 99.1% of the rows. |
| dimensao | 28681 | 0 | - | 27925 | 97.36% | 0 | - | 0 | - | 616 | Numeric | 97.36% | Anomalies present in 97.36% of the rows. |
| obra_incorporavel_patrimonio | 28681 | 0 | - | 26182 | 91.29% | 0 | - | 0 | - | 2 | Logical | 91.29% | Anomalies present in 91.29% of the rows. |
Vemos que exceto o número do protocolo tramita em que 0.05% das observações são nulas, nenhuma outra coluna tem observações nulas, porém a porcentagem de zeros chega a 97,36% na dimensão e 79,06% no valor da obra. Apesar de acusar 99,1% de zeros na coluna de obras canceladas, é uma coluna com tipo lógico, faz sentido que a maioria das obras não sejam canceladas, o que também acontece na coluna de obras incorporáveis ao patrimônio, onde acusa que 91,29% das obras tem valor zero.
Apesar da maioria das colunas não apresentarem valores nulos ou zeros, muitas delas possuem valores sem significado, por exemplo:
obra %>%
group_by(numero_contrato) %>%
summarise(quantidade = n()) %>%
arrange(-quantidade) %>%
head() %>%
kable()
| numero_contrato | quantidade |
|---|---|
| 0000 | 18567 |
| 000332011 | 39 |
| 000052010 | 34 |
| 000912008 | 33 |
| - | 30 |
| 000762015 | 29 |
Existem 18567 números de contrato 0000, assim como 30 com valor “-”.
Do mesmo modo, a coluna referente à descrição sucinta da obra tem alguns valores não nulos, mas sem significado:
obras.sem.descricao <- obra %>%
filter(str_count(descricao_sucinta_obra, "[:alpha:]|[:blank:]") / nchar(descricao_sucinta_obra) < 0.6)
obras.sem.descricao %>%
select(id, fk_jurisdicionado, numero_contrato, numero_obra, descricao_sucinta_obra, fk_localidade) %>%
head() %>%
kable()
| id | fk_jurisdicionado | numero_contrato | numero_obra | descricao_sucinta_obra | fk_localidade |
|---|---|---|---|---|---|
| 4223 | 616 | 0000 | 00102011 | 1 | 1254 |
| 5798 | 627 | 0000 | 00142003 | - | 1285 |
| 8443 | 642 | 0000 | 00112002 | - | 1245 |
| 8639 | 645 | 0000 | 00122003 | - | 1269 |
| 5794 | 627 | 0000 | 00132003 | - | 1285 |
| 369 | 31 | 0000 | 00022003 | - | 1369 |
(obras.sem.descricao %>% count()) / (obra %>% count()) * 100
## n
## 1 1.55155
Onde existem 1.5% de dados com mais de 40% da palavra composta por caracteres diferente do alfabeto.
Da mesma forma, a descrição da localidade tem vários valores sem significado:
obras.sem.localidade <- obra %>%
filter(str_count(descricao_localidade, "[:alpha:]|[:blank:]") / nchar(descricao_localidade) < 0.6)
obras.sem.localidade %>%
select(id, fk_jurisdicionado, numero_contrato, numero_obra, descricao_sucinta_obra, fk_localidade) %>%
head() %>%
kable()
| id | fk_jurisdicionado | numero_contrato | numero_obra | descricao_sucinta_obra | fk_localidade |
|---|---|---|---|---|---|
| 5798 | 627 | 0000 | 00142003 | - | 1285 |
| 8443 | 642 | 0000 | 00112002 | - | 1245 |
| 8639 | 645 | 0000 | 00122003 | - | 1269 |
| 5794 | 627 | 0000 | 00132003 | - | 1285 |
| 369 | 31 | 0000 | 00022003 | - | 1369 |
| 370 | 32 | 0000 | 00012003 | - | 1379 |
(obras.sem.localidade %>% count()) / (obra %>% count()) * 100
## n
## 1 1.589903
É possível ver que 1,58% das obras tem localidade composta por mais de 40% de caracteres diferente do alfabeto.
Vejamos os dados faltantes de jurisdicionados:
anomalies.jurisdicionado <- anomalies(jurisdicionado_db2)
## Warning in anomalies(jurisdicionado_db2): Found 2 possible problematic variables:
## previdenciario, cancelled
anomalies.jurisdicionado$variables %>%
kable()
| Variable | q | qNA | pNA | qZero | pZero | qBlank | pBlank | qInf | pInf | qDistinct | type | anomalous_percent |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| previdenciario | 1048 | 0 | - | 975 | 93.03% | 0 | - | 0 | - | 2 | Logical | 93.03% |
| cancelled | 1048 | 0 | - | 955 | 91.13% | 0 | - | 0 | - | 2 | Logical | 91.13% |
| codigo_sagres | 1048 | 0 | - | 296 | 28.24% | 0 | - | 0 | - | 685 | Character | 28.24% |
| esfera | 1048 | 102 | 9.73% | 0 | - | 0 | - | 0 | - | 4 | Integer | 9.73% |
| localidade_fk | 1048 | 8 | 0.76% | 0 | - | 0 | - | 0 | - | 230 | Numeric | 0.76% |
| municipio_importacao | 1048 | 4 | 0.38% | 0 | - | 0 | - | 0 | - | 230 | Character | 0.38% |
| poder | 1048 | 0 | - | 0 | - | 0 | - | 0 | - | 5 | Integer | - |
| tipojurisdicionado_fk | 1048 | 0 | - | 0 | - | 0 | - | 0 | - | 13 | Numeric | - |
| nome | 1048 | 0 | - | 0 | - | 0 | - | 0 | - | 1022 | Character | - |
| id | 1048 | 0 | - | 0 | - | 0 | - | 0 | - | 1048 | Numeric | - |
| pessoajuridica | 1048 | 0 | - | 0 | - | 0 | - | 0 | - | 1048 | Numeric | - |
| tramita_id | 1048 | 0 | - | 0 | - | 0 | - | 0 | - | 1048 | Numeric | - |
anomalies.jurisdicionado$problem_variables %>%
kable()
| Variable | q | qNA | pNA | qZero | pZero | qBlank | pBlank | qInf | pInf | qDistinct | type | anomalous_percent | problems |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| previdenciario | 1048 | 0 | - | 975 | 93.03% | 0 | - | 0 | - | 2 | Logical | 93.03% | Anomalies present in 93.03% of the rows. |
| cancelled | 1048 | 0 | - | 955 | 91.13% | 0 | - | 0 | - | 2 | Logical | 91.13% | Anomalies present in 91.13% of the rows. |
Vemos que em 28,24% dos dados, o código do sagres é vazio e em 9,73% dos dados da esfera, 0,76% da chave estrangeira da localidade e 0,38% do município de importação são nulos. Da mesma forma que o anterior, as colunas previdenciario e cancelled são do tipo boolean, logo, faz sentido a maior parte deles terem apenas um valor.
Vejamos agora os dados das localidades:
anomalies.localidade <- anomalies(localidade)
## Warning in anomalies(localidade): Found 7 possible problematic variables:
## cancelled, codigo_ibge, esfera, bandeira, brasao, link_ibge, link_wikipedia
anomalies.localidade$variables %>%
kable()
| Variable | q | qNA | pNA | qZero | pZero | qBlank | pBlank | qInf | pInf | qDistinct | type | anomalous_percent |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| cancelled | 5566 | 0 | - | 5566 | 100% | 0 | - | 0 | - | 1 | Logical | 100% |
| codigo_ibge | 5566 | 5342 | 95.98% | 1 | 0.02% | 0 | - | 0 | - | 225 | Integer | 95.99% |
| esfera | 5566 | 5342 | 95.98% | 0 | - | 0 | - | 0 | - | 2 | Integer | 95.98% |
| bandeira | 5566 | 5342 | 95.98% | 0 | - | 0 | - | 0 | - | 122 | Character | 95.98% |
| brasao | 5566 | 5342 | 95.98% | 0 | - | 0 | - | 0 | - | 193 | Character | 95.98% |
| link_ibge | 5566 | 5342 | 95.98% | 0 | - | 0 | - | 0 | - | 225 | Character | 95.98% |
| link_wikipedia | 5566 | 5342 | 95.98% | 0 | - | 0 | - | 0 | - | 225 | Character | 95.98% |
| codigo_siaf | 5566 | 0 | - | 68 | 1.22% | 0 | - | 0 | - | 5498 | Integer | 1.22% |
| codigo_uf | 5566 | 0 | - | 1 | 0.02% | 0 | - | 0 | - | 28 | Integer | 0.02% |
| codigo_mesoregiao | 5566 | 0 | - | 1 | 0.02% | 0 | - | 0 | - | 138 | Integer | 0.02% |
| codigo_microregiao | 5566 | 0 | - | 1 | 0.02% | 0 | - | 0 | - | 559 | Integer | 0.02% |
| uf | 5566 | 0 | - | 0 | - | 0 | - | 0 | - | 28 | Character | - |
| mesoregiao | 5566 | 0 | - | 0 | - | 0 | - | 0 | - | 138 | Character | - |
| microregiao | 5566 | 0 | - | 0 | - | 0 | - | 0 | - | 555 | Character | - |
| nome | 5566 | 0 | - | 0 | - | 0 | - | 0 | - | 5293 | Character | - |
| id | 5566 | 0 | - | 0 | - | 0 | - | 0 | - | 5566 | Numeric | - |
anomalies.localidade$problem_variables %>%
kable()
| Variable | q | qNA | pNA | qZero | pZero | qBlank | pBlank | qInf | pInf | qDistinct | type | anomalous_percent | problems |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| cancelled | 5566 | 0 | - | 5566 | 100% | 0 | - | 0 | - | 1 | Logical | 100% | Anomalies present in 100% of the rows. Less than 2 distinct values. |
| codigo_ibge | 5566 | 5342 | 95.98% | 1 | 0.02% | 0 | - | 0 | - | 225 | Integer | 95.99% | Anomalies present in 95.99% of the rows. |
| esfera | 5566 | 5342 | 95.98% | 0 | - | 0 | - | 0 | - | 2 | Integer | 95.98% | Anomalies present in 95.98% of the rows. |
| bandeira | 5566 | 5342 | 95.98% | 0 | - | 0 | - | 0 | - | 122 | Character | 95.98% | Anomalies present in 95.98% of the rows. |
| brasao | 5566 | 5342 | 95.98% | 0 | - | 0 | - | 0 | - | 193 | Character | 95.98% | Anomalies present in 95.98% of the rows. |
| link_ibge | 5566 | 5342 | 95.98% | 0 | - | 0 | - | 0 | - | 225 | Character | 95.98% | Anomalies present in 95.98% of the rows. |
| link_wikipedia | 5566 | 5342 | 95.98% | 0 | - | 0 | - | 0 | - | 225 | Character | 95.98% | Anomalies present in 95.98% of the rows. |
Vemos que várias colunas da localidade tem 95,98% dos valores faltantes, como codigo_ibge, esfera, bandeira, brasao, link_ibge e link_wikipedia.
Agora pelo complexo:
anomalies.complexo <- anomalies(complexo)
## Warning in anomalies(complexo): Found 1 possible problematic variables:
## cancelled
anomalies.complexo$variables %>%
kable()
| Variable | q | qNA | pNA | qZero | pZero | qBlank | pBlank | qInf | pInf | qDistinct | type | anomalous_percent |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| cancelled | 720 | 0 | - | 720 | 100% | 0 | - | 0 | - | 1 | Logical | 100% |
| tipo | 720 | 0 | - | 0 | - | 0 | - | 0 | - | 32 | Character | - |
| descricao | 720 | 0 | - | 0 | - | 0 | - | 0 | - | 694 | Character | - |
| id | 720 | 0 | - | 0 | - | 0 | - | 0 | - | 720 | Numeric | - |
| codigo | 720 | 0 | - | 0 | - | 0 | - | 0 | - | 720 | Character | - |
anomalies.complexo$problem_variables %>%
kable()
| Variable | q | qNA | pNA | qZero | pZero | qBlank | pBlank | qInf | pInf | qDistinct | type | anomalous_percent | problems |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| cancelled | 720 | 0 | - | 720 | 100% | 0 | - | 0 | - | 1 | Logical | 100% | Anomalies present in 100% of the rows. Less than 2 distinct values. |
Vemos que os dados de complexo parecem ser bem completos, não tendo dados faltantes, visto que a coluna cancelled é um boolean, onde nenhum complexo foi cancelado. Apesar disso, duas descrições não fazem sentido:
complexo %>%
filter(str_count(descricao, "[:alpha:]|[:blank:]") / nchar(descricao) < 0.6) %>%
kable()
| id | descricao | tipo | codigo | cancelled |
|---|---|---|---|---|
| 506 | 12412341234 | HOSPITAL | 506-5061 | FALSE |
| 505 | 21342134 | AÇUDE BARRAGEM | 505-5057 | FALSE |
Além disso, é possível ver que várias obras referenciam um mesmo complexo. Podemos ver a seguir os complexos com mais obras:
obra %>%
group_by(fk_complexo_obras) %>%
summarise(quantidade = n()) %>%
arrange(-quantidade) %>%
head() %>%
left_join(complexo, by = c("fk_complexo_obras" = "id")) %>%
kable()
| fk_complexo_obras | quantidade | descricao | tipo | codigo | cancelled |
|---|---|---|---|---|---|
| 298 | 27920 | IMPORTAÇÃO GEOPB | OUTRAS | 2982 | FALSE |
| 137 | 8 | CONSTRUÇÃO RE DE ÁGUA, REDE ESGOTO SANITÁRIO, REPOSIÇÃO DE CALÇAMENTO E PINTURA DE MEIO FIO | REDE COLETORA DE ESGOTO | 1376 | FALSE |
| 272 | 4 | Reposição de Pavimentação | PAVIMENTAÇÃO PARALEPÍPEDO | 2723 | FALSE |
| 676 | 4 | Sistema Integrado de Esgotamento Sanitário de João Pessoa | OUTRAS | 676-6761 | FALSE |
| 37 | 3 | REFORMA DE ESCOLAS | OUTRAS | 370 | FALSE |
| 81 | 3 | RECONSTRUÇÃO E REFORMA | GINÁSIO POLIESPORTIVO / QUADRA DE ESPORTES | 811 | FALSE |
Vemos que o complexo com mais obras é fictício, visto que a descrição é IMPORTAÇÃO GEOPB.
Por último, vejamos os dados faltantes da tabela de acompanhamento:
anomalies.acompanhamento <- anomalies(acompanhamento)
## Warning in anomalies(acompanhamento): Found 3 possible problematic variables:
## cancelled, tipo_georeferenciamento, valor_georeferenciamento
anomalies.acompanhamento$variables %>%
kable()
| Variable | q | qNA | pNA | qZero | pZero | qBlank | pBlank | qInf | pInf | qDistinct | type | anomalous_percent |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| cancelled | 56218 | 0 | - | 56218 | 100% | 0 | - | 0 | - | 1 | Logical | 100% |
| tipo_georeferenciamento | 56218 | 0 | - | 55598 | 98.9% | 0 | - | 0 | - | 4 | Integer | 98.9% |
| valor_georeferenciamento | 56218 | 0 | - | 55598 | 98.9% | 0 | - | 0 | - | 457 | Character | 98.9% |
| data_cadastro | 56218 | 120 | 0.21% | 0 | - | 0 | - | 0 | - | 3919 | Date | 0.21% |
| tipo | 56218 | 0 | - | 0 | - | 0 | - | 0 | - | 3 | Integer | - |
| fk_obra | 56218 | 0 | - | 0 | - | 0 | - | 0 | - | 28145 | Numeric | - |
| id | 56218 | 0 | - | 0 | - | 0 | - | 0 | - | 56218 | Numeric | - |
anomalies.acompanhamento$problem_variables %>%
kable()
| Variable | q | qNA | pNA | qZero | pZero | qBlank | pBlank | qInf | pInf | qDistinct | type | anomalous_percent | problems |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| cancelled | 56218 | 0 | - | 56218 | 100% | 0 | - | 0 | - | 1 | Logical | 100% | Anomalies present in 100% of the rows. Less than 2 distinct values. |
| tipo_georeferenciamento | 56218 | 0 | - | 55598 | 98.9% | 0 | - | 0 | - | 4 | Integer | 98.9% | Anomalies present in 98.9% of the rows. |
| valor_georeferenciamento | 56218 | 0 | - | 55598 | 98.9% | 0 | - | 0 | - | 457 | Character | 98.9% | Anomalies present in 98.9% of the rows. |
Vemos que tanto o tipo do georeferenciamento, quanto o valor do mesmo em 98,9% das linhas tem valor igual a zero.
Faremos agora uma análise sobre as obras que possuem valores distoantes das demais. Para isso vamos visualizar alguns gráficos para tentar entender melhor onde podem estar esses valores extremos.
Podemos ver que, de acordo com os dados, a mesorregião da Borborema é a que mais gasta com obras, com um gasto total de mais de 1 bilhão e 250 milhões de reais. Isso chama bastante a atenção pois era de se esperar que a mesorregião da Mata Paraibana concentrasse a maior parte dos gastos uma vez que esta contém a capital do estado e sua região metropolitana.
######################################PLOTANDO OS FRÁFICOS######################################
obras_por_mesoregiao %>%
filter(total_obras != 0) %>%
mutate(total_obras = total_obras/ UM_MILHAO) %>%
ggplot(aes(x = reorder(mesoregiao, -total_obras), y = total_obras)) +
geom_col() +
labs(title = "Gastos com obras por mesorregião", x="Mesorregião", y = "Valor gasto (milhões de R$)")
Agora veremos os gastos por microrregião para granularizar melhor as informações. De acordo com os dados a microrregião do Seridó Oriental Paraibano realizou gastos de mais de um bilhão de reais em obras, o que é no minímo alarmante pois essa microrregião possui uma população pequena e ainda assim gastou mais em obras do que a microrregião de João Pessoa que compreende a capital do estado e cidades populosas como Santa Rita e Bayeux.
obras_por_microregiao %>%
filter(total_obras != 0) %>%
mutate(total_obras = total_obras/ UM_MILHAO) %>%
ggplot(aes(x = reorder(microregiao, -total_obras), y = total_obras)) +
geom_col() +
labs(title = "Gastos com obras por microrregião", x="Microrregião", y = "Valor gasto (milhões de R$)") +
coord_flip()
Tendo em vista que a microrregião do Seridó Oriental é a que mais gasta, veremos agora os 20 municípios que mais gastam em obras para tentar identificar gastos que possam ter contribuido para o valor tão elevado visto anteriormente. O município de Cubati (que possui 8000 habitantes segundo o senso do IBGE de 2016) da mesorregião do Seridó Oriental realizou, segundo os dados, gastos superiores a 1 bilhão de reais, o que nos leva a entender que este município é responsável pelos valores extremos vistos anteriormente.
obras_por_municipio_top20 %>%
filter(total_obras != 0) %>%
mutate(total_obras = total_obras/ UM_MILHAO) %>%
ggplot(aes(x = reorder(municipio, -total_obras), y = total_obras)) +
geom_col() +
labs(title = "Top 20 dos municípios que mais gastam", x="Município", y = "Valor gasto (milhões de R$)") +
coord_flip()
A prefeitura de Cubati e o orgão que mais gasta, como era de devido ao gráfico anterior. A prefeitura do Conde também parece gastar além da conta, tendo gastos superiores a orgãos “maiores” como a Prefeitura de João Pessoa e a Prefeitura de Campina Grande.
obras_por_orgao_top20 %>%
filter(total_obras != 0) %>%
mutate(total_obras = total_obras/ UM_MILHAO) %>%
ggplot(aes(x = reorder(nome, -total_obras), y = total_obras)) +
geom_col() +
labs(title = "Top 20 dos orgãos públicos que mais gastam", x="Orgão", y = "Valor gasto (milhões de R$)") +
coord_flip()
Nesse gráfico temos os municípios que mais realizaram obras, se não levarmos em conta as obras que possuem o município “Não informado” (que podem ser obras de qualquer município) temos um resultado interessante, o município de Manaíra (com população de 11.000 habitantes segundo o censo do IBGE de 2016) tem uma quantidade de obras maior do que municípios como Cabedelo (67.000 habitantes segundo o censo do IBGE de 2016).
obras_por_orgao_quantidade_top20 %>%
ggplot(aes(x = reorder(municipio, -freq), y = freq)) +
geom_col() +
coord_flip() +
labs(title = "Top 20 dos municípios com maior quantidade de obras", x="Município", y = "Número de obras")
Agora que já temos uma noção de onde podem estar as obras com valores muito altos vamos utilziar boxplotes para tentar separá-las para posteriormente visualizá-las separadamente.
knitr::opts_chunk$set(fig.width=12, fig.height=8)
plotly::plot_ly(
join_obras_jurisdicionado %>% filter(valor_obra > UM_MILHAO) %>% group_by(mesoregiao),
x = ~mesoregiao,
y = ~log(valor_obra),
color = ~mesoregiao,
type = "box",
boxpoints = "suspectedoutliers"
) %>%
plotly::layout(title = "Obras com valores extremos (considerando apenas obras \ncom valor maior que um milhão)", xaxis=list(title="Mesorregião"), yaxis=list(title="Valor da obra em escala logarítimica"))
Graças ao gráfico acima, sabemos quais obras distoam das outras feitas em sua mesorregião e podemos mostrá-las em um gráfico interativo. No gráfico abaixo podemos ver essas obras e informações sobre elas como descrição, valor, mediana das obras da região e etc. Vejamos:
knitr::opts_chunk$set(fig.width=10, fig.height=8)
outliers_ggplot <-
outliers %>%
mutate(mediana_meso = lapply(mesoregiao, getMedianaMeso), mediana_micro = lapply(microregiao, getMedianaMicro)) %>%
ggplot(aes(y = valor_obra/UM_MILHAO, x = municipio, color = mesoregiao,
text = paste0("<b>Orgão: </b>", nome,
"<br><b>Valor:</b> R$", format(valor_obra/UM_MILHAO, digits = 2), " milhões",
"<br><b>Mediana da mesoregião: </b>R$", mediana_meso, " milhões",
"<br><b>Mediana da microrregião: </b>R$", mediana_micro, " milhões",
"<br><b>Local da obra: </b>", breakString(descricao_localidade),
"<b>Descrição: </b>", breakString(descricao_sucinta_obra)))) +
geom_point(alpha = .7) +
coord_flip() +
labs(title = "Obras com valores extremos", x="Município", y = "Valor da obra em milhões de R$")
plotly::ggplotly(outliers_ggplot, tooltip=c("text"))
Após uma análise sobre os dados relativos à obras, destacamos alguns pontos importantes:
Algumas destas observações são características de dados legados, onde existem colunas cujos valores tem significados especiais, porém identificamos os mesmos para entender como lidar com estas características do banco de dados.